package Colecciones;

import Modelo.EjecutaConsulta;
import Modelo.Fila;
import java.io.PrintStream;
import java.util.Vector;

public class Empresa
{
    public static final String PHONE_TYPE_BUSN = "BUSN";
    public static final String PHONE_TYPE_CELL = "CELL";
    public static final String PHONE_TYPE_FAX = "FAX";
    public static final String EXT_ORG_TYPE_BUSN = "BUSN";
    public static final String EXT_ORG_TYPE_SCHL = "SCHL";
    public static final String PROPRIETORSHIP_RELG = "RELG";
    public static final String PROPRIETORSHIP_PUBL = "PUBL";
    public static final String PROPRIETORSHIP_PRIV = "PRIV";
    public static final String PROPRIETORSHIP_OTHR = "OTHR";

    public static boolean crearNuevaEmpresa(Fila filaDatosEmpresa, Fila filaDatosUbicacion) throws Exception {
        String strInsert = "INSERT INTO PS_EXT_ORG_TBL";

        String strCampos="(EXT_ORG_ID, EFFDT, EFF_STATUS, LASTUPDDTTM, SETID, VENDOR_ID, TAXPAYER_ID_NO";
        String strValues = " VALUES ('"+filaDatosEmpresa.getValor("EXT_ORG_ID")+"',to_date(to_char(SYSDATE, 'dd/mm/yyyy'), 'dd/mm/yyyy'), 'A', SYSDATE, ' ', ' ', ' '";

        if(filaDatosEmpresa.getValor("OTH_NAME_SORT_SRCH") != null){
            strCampos+=", OTH_NAME_SORT_SRCH";
            strValues += ",'" + filaDatosEmpresa.getValor("OTH_NAME_SORT_SRCH").replace(" ", "").toUpperCase() + "'";
        }

        if(filaDatosEmpresa.getValor("EXT_ORG_TYPE") != null){
            strCampos+=", EXT_ORG_TYPE";
            strValues += ",'" + filaDatosEmpresa.getValor("EXT_ORG_TYPE") + "'";
        }

        if(filaDatosEmpresa.getValor("DESCR") != null){
            strCampos+=", DESCR";
            strValues += ",'" + filaDatosEmpresa.getValor("DESCR") + "'";
        }

        if(filaDatosEmpresa.getValor("DESCR50") != null){
            strCampos+=", DESCR50";
            strValues += ",'" + filaDatosEmpresa.getValor("DESCR50") + "'";
        }

        if(filaDatosEmpresa.getValor("DESCRSHORT") != null){
            strCampos+=", DESCRSHORT";
            strValues += ",'" + filaDatosEmpresa.getValor("DESCRSHORT") + "'";
        }

        if(filaDatosEmpresa.getValor("ORG_CONTACT") != null){
            strCampos+=", ORG_CONTACT";
            strValues += ",'" + filaDatosEmpresa.getValor("ORG_CONTACT") + "'";
        }

        if(filaDatosEmpresa.getValor("ORG_LOCATION") != null){
            strCampos+=", ORG_LOCATION";
            strValues += ",'" + filaDatosEmpresa.getValor("ORG_LOCATION") + "'";
        }

        if(filaDatosEmpresa.getValor("ORG_DEPARTMENT") != null){
            strCampos+=", ORG_DEPARTMENT";
            strValues += ",'" + filaDatosEmpresa.getValor("ORG_DEPARTMENT") + "'";
        }

        if(filaDatosEmpresa.getValor("PROPRIETORSHIP") != null){
            strCampos+=", PROPRIETORSHIP";
            strValues += ",'" + filaDatosEmpresa.getValor("PROPRIETORSHIP") + "'";
        }

        if(filaDatosEmpresa.getValor("LASTUPDOPRID") != null){
            strCampos+=", LASTUPDOPRID";
            strValues += ",'" + filaDatosEmpresa.getValor("LASTUPDOPRID").toUpperCase() + "'";
        }

        strCampos += ")";
        strValues += ")";
        String strQuery=strInsert+strCampos+strValues;

        //if (bSalida) {
            String strInsertLoc = "INSERT INTO PS_ORG_LOCATION";

            String strCamposLoc ="(EXT_ORG_ID, ORG_LOCATION, EFFDT, EFF_STATUS, LASTUPDDTTM, ADDRESS3, ADDRESS4, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3, POSTAL, GEO_CODE, IN_CITY_LIMIT, EDI_ADDRESS";
            String strValuesLoc = " VALUES ('"+filaDatosUbicacion.getValor("EXT_ORG_ID")+"', '"+filaDatosUbicacion.getValor("ORG_LOCATION")+"',to_date(to_char(SYSDATE, 'dd/mm/yyyy'), 'dd/mm/yyyy'), 'A', SYSDATE, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '";

            if(filaDatosUbicacion.getValor("DESCR") != null){
                strCamposLoc +=", DESCR";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("DESCR") + "'";
            }

            if(filaDatosUbicacion.getValor("DESCRSHORT") != null){
                strCamposLoc +=", DESCRSHORT";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("DESCRSHORT") + "'";
            }

            if(filaDatosUbicacion.getValor("COUNTRY") != null){
                strCamposLoc +=", COUNTRY";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("COUNTRY") + "'";
            }

            if(filaDatosUbicacion.getValor("ADDRESS1") != null){
                strCamposLoc +=", ADDRESS1";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("ADDRESS1") + "'";
            }

            if(filaDatosUbicacion.getValor("ADDRESS2") != null){
                strCamposLoc+=", ADDRESS2";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("ADDRESS2") + "'";
            }

            if(filaDatosUbicacion.getValor("COUNTY") != null){
                strCamposLoc+=", COUNTY";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("COUNTY") + "'";
            }

            if(filaDatosUbicacion.getValor("CITY") != null){
                strCamposLoc+=", CITY";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("CITY") + "'";
            }

            if(filaDatosUbicacion.getValor("STATE") != null){
                strCamposLoc+=", STATE";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("STATE") + "'";
            }

            if(filaDatosUbicacion.getValor("URL_ADDRESS") != null){
                strCamposLoc+=", URL_ADDRESS";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("URL_ADDRESS") + "'";
            }

            if(filaDatosUbicacion.getValor("EMAILID") != null){
                strCamposLoc+=", EMAILID";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("EMAILID") + "'";
            }

            if(filaDatosUbicacion.getValor("LASTUPDOPRID") != null){
                strCamposLoc+=", LASTUPDOPRID";
                strValuesLoc += ",'" + filaDatosUbicacion.getValor("LASTUPDOPRID").toUpperCase() + "'";
            }

            strCamposLoc += ")";
            strValuesLoc += ")";
            String strQueryLoc=strInsertLoc+strCamposLoc+strValuesLoc;
        //}

        //System.out.println("INSERTAR EXT_ORG: " + strQuery);
        //System.out.println("INSERTAR ORG_LOC: " + strQueryLoc);

        boolean bSalida=EjecutaConsulta.Insertar(strQuery);

        if (bSalida) {
            bSalida = EjecutaConsulta.Insertar(strQueryLoc);

            if (!bSalida) {
                //Debe borrar el registro de Empresas...
                strQuery = "DELETE FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID = '"+filaDatosEmpresa.getValor("EXT_ORG_ID")+"'";
                EjecutaConsulta.Insertar(strQueryLoc);
            }
        }

        return bSalida;
    }

  public static Vector getContactosPorEmpresa(String p_ext_org_id, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = "SELECT EMP.EXT_ORG_ID, EMP.EFFDT, CONT.EFF_STATUS, EMPLID, CONT.ORG_CONTACT, " +
              "CONT.CONTACT_NAME AS NOMBRES, CONT.JOBTITLE AS NOMBRE_CARGO, CONT.EMAILID AS CORREO, " +
              "(SELECT PHN.PHONE from PS_ORG_CNTCT_PHN PHN WHERE PHN.EXT_ORG_ID = EMP.EXT_ORG_ID AND CONT.ORG_CONTACT = PHN.ORG_CONTACT AND (PHN.PHONE_TYPE = 'BUSN' OR PHN.PHONE_TYPE = 'WORK' OR PHN.PHONE_TYPE = 'MAIN') AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_CNTCT_PHN PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.ORG_CONTACT = CONT.ORG_CONTACT) AND ROWNUM <= 1) AS TELEFONO_OFICINA, " +
              "(SELECT PHN.PHONE from PS_ORG_CNTCT_PHN PHN WHERE PHN.EXT_ORG_ID = EMP.EXT_ORG_ID AND CONT.ORG_CONTACT = PHN.ORG_CONTACT AND (PHN.PHONE_TYPE = 'CELL') AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_CNTCT_PHN PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.ORG_CONTACT = CONT.ORG_CONTACT) AND ROWNUM <= 1) AS NUMERO_CELULAR " +
              "FROM PS_EXT_ORG_TBL EMP, PS_ORG_CONTACT CONT " +
              "WHERE EMP.EXT_ORG_ID = CONT.EXT_ORG_ID " +
              "AND CONT.EFF_STATUS = 'A' " +
              "AND EMP.EXT_ORG_ID = '" + p_ext_org_id + "' " +
              "AND EMP.EFFDT = (SELECT MAX(EMP_IN.EFFDT) FROM PS_EXT_ORG_TBL EMP_IN WHERE EMP_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) " +
              "AND CONT.EFFDT = (SELECT MAX(CONT_IN.EFFDT) FROM PS_ORG_CONTACT CONT_IN WHERE CONT_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND CONT_IN.ORG_CONTACT = CONT.ORG_CONTACT)";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);
      }

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getContactosPorEmpresa " + e.toString());
      e.printStackTrace();
    }
    return datos;
  }

    public static int getNuevoExtOrgId() throws Exception {
        int iNuevoOrgId = -1;

        String strQuery = "SELECT EXT_ORG_ID_LAST FROM PS_INSTALLATION_SA";
        Vector vDatos = EjecutaConsulta.Ejecutar(strQuery);

        if (vDatos.size() > 0) {
          Fila filaDato = (Fila) vDatos.firstElement();
            try {
                iNuevoOrgId = Integer.parseInt(filaDato.getValor("EXT_ORG_ID_LAST"));
                iNuevoOrgId = iNuevoOrgId + 1;
            } catch (Exception e) {}
        }

        if (iNuevoOrgId > 0) {
            //Obtuvo el nuevo EXT_ORG_ID
            strQuery = "UPDATE PS_INSTALLATION_SA SET EXT_ORG_ID_LAST = " + iNuevoOrgId;
            boolean bResult = EjecutaConsulta.Insertar(strQuery);

            if (!bResult) {
                iNuevoOrgId = -1;
            }
        }

        return iNuevoOrgId;
    }

    public static int getNuevoOrgContact(String strExtOrgId) throws Exception{
        int iNuevoOrgContact = 1;

        String strQuery = "SELECT MAX(CONT.ORG_CONTACT) AS ORG_CONTACT FROM PS_ORG_CONTACT CONT WHERE CONT.EXT_ORG_ID = '"+strExtOrgId+"'";
        Vector vDatos = EjecutaConsulta.Ejecutar(strQuery);

        if (vDatos.size() > 0) {
          Fila filaDato = (Fila) vDatos.firstElement();
            try {
                iNuevoOrgContact = Integer.parseInt(filaDato.getValor("ORG_CONTACT"));
                iNuevoOrgContact = iNuevoOrgContact + 1;
            } catch (Exception e) {}
        }

        return iNuevoOrgContact;
    }

    public static boolean modificarContacto(Fila filaContactoPeople) throws Exception {
        boolean bResultado = false;

        //Consulta El registro de contacto...
        Fila filaUltimoRegistro = getUltimoRegistroContacto(filaContactoPeople.getValor("EXT_ORG_ID"), filaContactoPeople.getValor("ORG_CONTACT"));
        
        //Ajusta los nuevos valores...
        filaUltimoRegistro.setValor("CONTACT_NAME", filaContactoPeople.getValor("CONTACT_NAME"));
        filaUltimoRegistro.setValor("EMAILID", filaContactoPeople.getValor("EMAILID"));
        filaUltimoRegistro.setValor("JOBTITLE", filaContactoPeople.getValor("JOBTITLE"));
        filaUltimoRegistro.setValor("LASTUPDOPRID", filaContactoPeople.getValor("LASTUPDOPRID"));
        
        //Inserta un nuevo registro de contacto...
        bResultado = insertarNuevoRegistroContacto(filaUltimoRegistro);

        return bResultado;
    }

    private static Fila getUltimoRegistroContacto(String strExtOrgId, String strOrgContact) {
        Vector vDatos = new Vector();
        Fila filaDato = new Fila();
        try
        {
          String consulta = "SELECT CONT.EXT_ORG_ID, CONT.ORG_CONTACT, CONT.EFF_STATUS, CONT.EMPLID, " +
                  "CONT.CONTACT_NAME, CONT.JOBTITLE, CONT.ORG_LOCATION, CONT.ORG_DEPARTMENT, CONT.EMAILID, " +
                  "CONT.ORG_CONTACT_TYPE, CONT.URL_ADDRESS, CONT.SCC_CNTC_ADDR_TYPE, CONT.ADDRESS_TYPE, CONT.LASTUPDOPRID " +
                  "FROM PS_ORG_CONTACT CONT " +
                  "WHERE CONT.EXT_ORG_ID = '"+strExtOrgId+"' " +
                  "AND CONT.ORG_CONTACT = '"+strOrgContact+"' " +
                  "AND CONT.EFFDT = (SELECT MAX(CONT_IN.EFFDT) " +
                        "FROM PS_ORG_CONTACT CONT_IN WHERE CONT_IN.EXT_ORG_ID = '"+strExtOrgId+"' " +
                        "AND CONT_IN.ORG_CONTACT = '"+strOrgContact+"')";

          vDatos = EjecutaConsulta.Ejecutar(consulta);
          if (vDatos.size() > 0) {
              filaDato = (Fila) vDatos.firstElement();
          }
        } catch (Exception e) {
          e.printStackTrace();
        }
        return filaDato;
    }

    public static boolean insertarNuevoRegistroContacto(Fila filaRegistro) throws Exception {
        String strInsert = "INSERT INTO PS_ORG_CONTACT";

        String strCampos="(EXT_ORG_ID, ORG_CONTACT, EFFDT, EFF_STATUS, LASTUPDDTTM";
        String strValues = " VALUES ('"+filaRegistro.getValor("EXT_ORG_ID")+"','"+filaRegistro.getValor("ORG_CONTACT")+"', SYSDATE, 'A', SYSDATE";

        if(filaRegistro.getValor("EMPLID") != null){
            strCampos+=",EMPLID";
            strValues += ",'" + filaRegistro.getValor("EMPLID") + "'";
        }

        if(filaRegistro.getValor("CONTACT_NAME") != null && filaRegistro.getValor("CONTACT_NAME").length() > 0){
            strCampos+=",CONTACT_NAME";
            strValues += ",'" + filaRegistro.getValor("CONTACT_NAME") + "'";
        }

        if(filaRegistro.getValor("JOBTITLE") != null){
            strCampos+=",JOBTITLE";
            strValues += ",'" + filaRegistro.getValor("JOBTITLE") + "'";
        }

        if(filaRegistro.getValor("ORG_LOCATION") != null){
            strCampos+=",ORG_LOCATION";
            strValues += ",'" + filaRegistro.getValor("ORG_LOCATION") + "'";
        }

        if(filaRegistro.getValor("ORG_DEPARTMENT") != null){
            strCampos+=",ORG_DEPARTMENT";
            strValues += ",'" + filaRegistro.getValor("ORG_DEPARTMENT") + "'";
        }

        if(filaRegistro.getValor("EMAILID") != null){
            strCampos+=",EMAILID";
            strValues += ",'" + filaRegistro.getValor("EMAILID") + "'";
        }

        if(filaRegistro.getValor("ORG_CONTACT_TYPE") != null){
            strCampos+=",ORG_CONTACT_TYPE";
            strValues += ",'" + filaRegistro.getValor("ORG_CONTACT_TYPE") + "'";
        }

        if(filaRegistro.getValor("URL_ADDRESS") != null){
            strCampos+=",URL_ADDRESS";
            strValues += ",'" + filaRegistro.getValor("URL_ADDRESS") + "'";
        }

        if(filaRegistro.getValor("SCC_CNTC_ADDR_TYPE") != null){
            strCampos+=",SCC_CNTC_ADDR_TYPE";
            strValues += ",'" + filaRegistro.getValor("SCC_CNTC_ADDR_TYPE") + "'";
        }

        if(filaRegistro.getValor("ADDRESS_TYPE") != null){
            strCampos+=",ADDRESS_TYPE";
            strValues += ",'" + filaRegistro.getValor("ADDRESS_TYPE") + "'";
        }

        if(filaRegistro.getValor("LASTUPDOPRID") != null){
            strCampos+=",LASTUPDOPRID";
            strValues += ",'" + filaRegistro.getValor("LASTUPDOPRID").toUpperCase() + "'";
        }

        strCampos += ")";
        strValues += ")";
        String strQuery=strInsert+strCampos+strValues;
        //System.out.println("INSERTAR: " + strQuery);

        boolean bSalida=EjecutaConsulta.Insertar(strQuery);

        return bSalida;
    }

    public static boolean agregarTelefonoContacto(Fila filaTelefono) throws Exception {
        boolean bResult = false;
        Fila filaUltimoRegistro = getUltimoRegistroTelefonoContacto(filaTelefono.getValor("EXT_ORG_ID"), filaTelefono.getValor("ORG_CONTACT"), filaTelefono.getValor("PHONE_TYPE"));

        if (filaUltimoRegistro.size() > 0) {
            //Ya tenia un numero asignado...
            filaUltimoRegistro.setValor("PHONE", filaTelefono.getValor("PHONE"));
        } else {
            //No tenia un numero previo...
            filaUltimoRegistro = new Fila();
            filaUltimoRegistro.adicionar("EXT_ORG_ID", filaTelefono.getValor("EXT_ORG_ID"));
            filaUltimoRegistro.adicionar("ORG_CONTACT", filaTelefono.getValor("ORG_CONTACT"));
            filaUltimoRegistro.adicionar("PHONE_TYPE", filaTelefono.getValor("PHONE_TYPE"));
            filaUltimoRegistro.adicionar("COUNTRY_CODE", " ");
            filaUltimoRegistro.adicionar("PHONE", filaTelefono.getValor("PHONE"));
            filaUltimoRegistro.adicionar("EXTENSION", " ");
            filaUltimoRegistro.adicionar("PREF_PHONE_FLAG", "Y");
        }

        bResult = insertarNuevoRegistroTelefonoContacto(filaUltimoRegistro);
        
        return bResult;
    }

    private static Fila getUltimoRegistroTelefonoContacto(String strExtOrgId, String strOrgContact, String strPhoneType) {
        Vector vDatos = new Vector();
        Fila filaDato = new Fila();
        try
        {
          String consulta = "SELECT PHN.EXT_ORG_ID, PHN.ORG_CONTACT, PHN.PHONE_TYPE, PHN.COUNTRY_CODE, " +
                  "PHN.PHONE, PHN.EXTENSION, PHN.PREF_PHONE_FLAG " +
                  "FROM PS_ORG_CNTCT_PHN PHN " +
                  "WHERE PHN.EXT_ORG_ID = '"+strExtOrgId+"' " +
                  "AND PHN.ORG_CONTACT = '"+strOrgContact+"' " +
                  "AND PHN.EFFDT = " +
                  "(SELECT MAX(PHN_IN.EFFDT) " +
                  "FROM PS_ORG_CNTCT_PHN PHN_IN " +
                  "WHERE PHN_IN.EXT_ORG_ID = '"+strExtOrgId+"' AND PHN_IN.ORG_CONTACT = '"+strOrgContact+"') " +
                  "AND PHN.PHONE_TYPE = '"+strPhoneType+"'";

          vDatos = EjecutaConsulta.Ejecutar(consulta);
          if (vDatos.size() > 0) {
              filaDato = (Fila) vDatos.firstElement();
          }
        } catch (Exception e) {
          e.printStackTrace();
        }
        return filaDato;
    }

    private static boolean insertarNuevoRegistroTelefonoContacto(Fila filaRegistro) throws Exception {
        String strInsert = "INSERT INTO PS_ORG_CNTCT_PHN";

        String strCampos="(EXT_ORG_ID, ORG_CONTACT, EFFDT, PREF_PHONE_FLAG";
        String strValues = " VALUES ('"+filaRegistro.getValor("EXT_ORG_ID")+"','"+filaRegistro.getValor("ORG_CONTACT")+"', SYSDATE, 'Y'";

        if(filaRegistro.getValor("PHONE_TYPE") != null){
            strCampos+=",PHONE_TYPE";
            strValues += ",'" + filaRegistro.getValor("PHONE_TYPE") + "'";
        }

        if(filaRegistro.getValor("COUNTRY_CODE") != null){
            strCampos+=",COUNTRY_CODE";
            strValues += ",'" + filaRegistro.getValor("COUNTRY_CODE") + "'";
        }

        if(filaRegistro.getValor("PHONE") != null){
            strCampos+=",PHONE";
            strValues += ",'" + filaRegistro.getValor("PHONE") + "'";
        }

        if(filaRegistro.getValor("EXTENSION") != null){
            strCampos+=",EXTENSION";
            strValues += ",'" + filaRegistro.getValor("EXTENSION") + "'";
        }

        strCampos += ")";
        strValues += ")";
        String strQuery=strInsert+strCampos+strValues;
        //System.out.println("INSERTAR PHONE: " + strQuery);

        boolean bSalida=EjecutaConsulta.Insertar(strQuery);

        return bSalida;
    }

  public static Vector getEmpresasPorCiudad(String p_city, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String strTablas = "FROM PS_EXT_ORG_TBL EMP, PS_ORG_LOCATION LOC";

      String consulta = " SELECT EMP.EXT_ORG_ID,  EMP.EFFDT,  EMP.EFF_STATUS,  EMP.DESCR NOMBRE,  LOC.DESCR,  LOC.CITY" + strTablas + " WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID" + "  AND LOWER(LOC.CITY) = LOWER('" + p_city + "')";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY EMP.DESCR";

      datos = EjecutaConsulta.Ejecutar(consulta);

      if (datos.size() == 0) {
        consulta = " SELECT EMP.EXT_ORG_ID,  EMP.EFFDT,  EMP.EFF_STATUS,  EMP.DESCR NOMBRE,  LOC.DESCR,  LOC.CITY" + strTablas + " WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID" + "  AND LOWER(LOC.CITY) LIKE LOWER('%" + p_city + "%')";

        if (p_reg > 0)
          consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

        consulta = consulta + "  ORDER BY EMP.DESCR";

        datos = EjecutaConsulta.Ejecutar(consulta);
      }
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getEmpresasPorCiudad " + e.toString());
    }
    return datos;
  }

  public static Vector getEmpresasPorCiudad2(String p_ext_org_id, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String strTablas = "FROM PS_EXT_ORG_TBL EMP, PS_ORG_LOCATION LOC";

      String consulta = " SELECT EMP.EXT_ORG_ID,  EFFDT,  EFF_STATUS,  DESCR NOMBRE,  LOC.DESCR,  LOC.CITY" + strTablas + " WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID" + "  AND LOC.EXT_ORG_ID = '" + p_ext_org_id + "' ";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY DESCR";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getEmpresasPorCiudad2 " + e.toString());
    }
    return datos;
  }

  public static Vector listadoEmpresas(int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = "SELECT EMP.EXT_ORG_ID CONSECUTIVO, TRANSLATE(EMP.DESCR, '\"', '`')  NOMBRE, EMP.EXT_ORG_TYPE TIPO_EMPRESA, " +
              "(SELECT PHN.PHONE FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'BUSN' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) TELEFONO, " +
              "(SELECT PHN.EXTENSION FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'BUSN' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) EXTENSION, " +
              "TRANSLATE((SELECT LOC.ADDRESS1 FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1), '\"', '`') DIRECCION, " +
              "(SELECT LOC.COUNTRY FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) PAIS, " +
              "(SELECT LOC.STATE FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) DEP_CONSECUTIVO, " +
              "(SELECT LOC.CITY FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) CIU_CONSECUTIVO, " +
              "(SELECT LOC.EMAILID FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) CORREO " +
              "FROM PS_EXT_ORG_TBL EMP " +
              "WHERE EMP.EFFDT = (SELECT MAX(EMP_IN.EFFDT) FROM PS_EXT_ORG_TBL EMP_IN WHERE EMP_IN.EXT_ORG_ID = EMP.EXT_ORG_ID)";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY LOWER(EMP.DESCR) ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.listadoEmpresas " + e.toString());
      e.printStackTrace();
    }
    return datos;
  }

  public static Vector listadoEmpresas()
  {
    return listadoEmpresas(0);
  }

  public static Vector consultarEmpresas(String strNombreEmpresa, int p_reg)
  {
    Vector datos = new Vector();
    try
    {

      String consulta = "SELECT EMP.EXT_ORG_ID CONSECUTIVO, TRANSLATE(EMP.DESCR, '\"', '`')  NOMBRE, EMP.EXT_ORG_TYPE TIPO_EMPRESA, " +
              "(SELECT PHN.PHONE FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'BUSN' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) TELEFONO, " +
              "(SELECT PHN.EXTENSION FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'BUSN' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) EXTENSION, " +
              "TRANSLATE((SELECT LOC.ADDRESS1 FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1), '\"', '`') DIRECCION, " +
              "(SELECT LOC.COUNTRY FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) PAIS, " +
              "(SELECT LOC.STATE FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) DEP_CONSECUTIVO, " +
              "(SELECT LOC.CITY FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) CIU_CONSECUTIVO, " +
              "(SELECT LOC.EMAILID FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) CORREO " +
              "FROM PS_EXT_ORG_TBL EMP " +
              "WHERE LOWER(EMP.DESCR) LIKE LOWER('"+strNombreEmpresa+"%') " +
              "AND EMP.EFFDT = (SELECT MAX(EMP_IN.EFFDT) FROM PS_EXT_ORG_TBL EMP_IN WHERE EMP_IN.EXT_ORG_ID = EMP.EXT_ORG_ID)";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY LOWER(EMP.DESCR)";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
        e.printStackTrace();
      System.out.println("Error: en la consulta de Empresa.consultarEmpresas " + e.toString());
    }
    return datos;
  }

  /*
  public static Vector contactosPorEmpresa(String ext_org_id)
  {
    Vector datos = new Vector();
    try
    {
      String strTablas = " FROM PS_EXT_ORG_TBL EMP, PS_ORG_CONTACT CON, PS_ORG_CNTCT_PHN PHN";

      String consulta = " SELECT   CON.EXT_ORG_ID CONSECUTIVO,   CON.CONTACT_NAME NOMBRE,   PHN.PHONE TELEFONO,   PHN.PHONE_TYPE TIPO_TELEFONO,   EMP.ADDRESS1 DIRECCION" + strTablas + " WHERE" + "   EMP.EXT_ORG_ID = '" + ext_org_id + "' " + "   AND con.ext_org_id = EMP.EXT_ORG_ID" + "   AND phn.ext_org_id = EMP.EXT_ORG_ID";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.contactosPorEmpresa " + e.toString());
    }
    return datos;
  }
   */

  public static Vector listadoTiposEmpresa(int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = "SELECT CODIGO AS CONSECUTIVO, NOMBRE FROM TIPOS_ACTIVIDAD";

      if (p_reg > 0)
        consulta = consulta + " WHERE ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY LOWER(NOMBRE)";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.listadoEmpresas " + e.toString());
    }
    return datos;
  }

  public static String obtenerNombreTipoEmpresa(String strCodigoActividad) {
    Vector datos = new Vector();
    String strNombre = new String();
    try {
      String consulta = "SELECT NOMBRE FROM TIPOS_ACTIVIDAD WHERE CODIGO = '"+strCodigoActividad+"'";
      datos = EjecutaConsulta.Ejecutar(consulta);

      if (datos.size() > 0) {
          Fila filaTMP = (Fila) datos.firstElement();
          strNombre = filaTMP.getValor("NOMBRE");
      }
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.obtenerNombreTipoEmpresa " + e.toString());
    }
    return strNombre;
  }

  public static Vector listadoTiposEmpresa()
  {
    return listadoTiposEmpresa(0);
  }

  public static Vector buscarEmpresa(String nombre, String ciudad)
  {
    Vector datos = new Vector();
    try
    {
      String strTablas = " FROM PS_EXT_ORG_TBL EMP, PS_ORG_LOCATION LOC, PS_ORG_LOC_PHONES PHN";

      String consulta = " SELECT  EMP.EXT_ORG_ID CONSECUTIVO,  EMP.DESCR NOMBRE,  EMP.EXT_ORG_TYPE TIPO_EMPRESA,  LOC.CITY CIUDAD,  PHN.PHONE TELEFONO,   '' EXTENSION,   LOC.DESCR DIRECCION " + strTablas + " WHERE" + "  EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND" + "  EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND" + "  EMP.OTH_NAME_SORT_SRCH LIKE '%" + nombre + "%' AND" + "  EMP.CITY CIUDAD '%" + ciudad + "%'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
        e.printStackTrace();
      System.out.println("Error: en la consulta de Empresa.buscarEmpresa " + e.toString());
    }
    return datos;
  }

  public static Vector buscarEmpresa(String extOrgId)
  {
    Vector datos = new Vector();
    try {
        String consulta = "SELECT EMP.EXT_ORG_ID CONSECUTIVO, TRANSLATE(EMP.DESCR, '\"', '`')  NOMBRE, EMP.EXT_ORG_TYPE TIPO_EMPRESA, " +
              "(SELECT PHN.PHONE FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'BUSN' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.PHONE_TYPE = 'BUSN') AND ROWNUM <= 1) TELEFONO, " +
              "(SELECT PHN.EXTENSION FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'BUSN' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.PHONE_TYPE = 'BUSN') AND ROWNUM <= 1) EXTENSION, " +
              "(SELECT PHN.PHONE FROM PS_ORG_LOC_PHONES PHN WHERE EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND PHN.PHONE_TYPE = 'FAX' AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_LOC_PHONES PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.PHONE_TYPE = 'FAX') AND ROWNUM <= 1) FAX, " +
              "TRANSLATE((SELECT LOC.ADDRESS1 FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1), '\"', '`') DIRECCION, " +
              "(SELECT LOC.COUNTRY FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) PAIS, " +
              "(SELECT LOC.STATE FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) DEP_CONSECUTIVO, " +
              "(SELECT LOC.CITY FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) CIU_CONSECUTIVO, " +
              "(SELECT LOC.EMAILID FROM PS_ORG_LOCATION LOC WHERE EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND LOC.EFFDT = (SELECT MAX(LOC_IN.EFFDT) FROM PS_ORG_LOCATION LOC_IN WHERE LOC_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) AND ROWNUM <= 1) CORREO " +
              "FROM PS_EXT_ORG_TBL EMP " +
              "WHERE EMP.EXT_ORG_ID = '"+extOrgId+"' " +
              "AND EMP.EFFDT = (SELECT MAX(EMP_IN.EFFDT) FROM PS_EXT_ORG_TBL EMP_IN WHERE EMP_IN.EXT_ORG_ID = EMP.EXT_ORG_ID)";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
        e.printStackTrace();
      System.out.println("Error: en la consulta de Empresa.buscarEmpresa " + e.toString());
    }
    return datos;
  }

  public static String obtenerConsecutivoEmpresa(String strNombreEmpresa)
  {
    String strConsecutivo = "";

    String strTablas = " FROM PS_EXT_ORG_TBL";
    try
    {
      String strSql = "SELECT EXT_ORG_ID" + strTablas + " WHERE LOWER(DESCR) = LOWER('" + strNombreEmpresa + "')";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);
      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("EXT_ORG_ID");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static String obtenerConsecutivoCargo(String strNombreCargo)
  {
    String strConsecutivo = "";
    try
    {
      String strSql = "SELECT US_SOC_CD FROM PS_US_SOC_TBL WHERE LOWER(DESCR) = LOWER('" + strNombreCargo + "')";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);
      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("US_SOC_CD");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static String obtenerNombreEmpresa(String strConsecutivoEmpresa)
  {
    String strConsecutivo = "";

    try
    {
      String strSql = "SELECT EMP.DESCR FROM PS_EXT_ORG_TBL EMP WHERE EMP.EXT_ORG_ID = '"+strConsecutivoEmpresa+"' AND EMP.EFFDT = (SELECT MAX(EMP_IN.EFFDT) FROM PS_EXT_ORG_TBL EMP_IN WHERE EMP_IN.EXT_ORG_ID = EMP.EXT_ORG_ID)";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);

      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("DESCR");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static String obtenerNombreCargo(String strConsecutivoCargo)
  {
    String strConsecutivo = "";
    try
    {
      String strSql = "SELECT DESCR FROM PS_US_SOC_TBL WHERE US_SOC_CD = '" + strConsecutivoCargo + "'";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);

      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("DESCR");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static boolean agregarTelefonoEmpresa(Fila filaTelefono) throws Exception {
        boolean bResult = false;
        Fila filaUltimoRegistro = getUltimoRegistroTelefonoEmpresa(filaTelefono.getValor("EXT_ORG_ID"), filaTelefono.getValor("ORG_LOCATION"), filaTelefono.getValor("PHONE_TYPE"));

        if (filaUltimoRegistro.size() > 0) {
            //Ya tenia un numero asignado...
            filaUltimoRegistro.setValor("PHONE", filaTelefono.getValor("PHONE"));
        } else {
            //No tenia un numero previo...
            filaUltimoRegistro = new Fila();
            filaUltimoRegistro.adicionar("EXT_ORG_ID", filaTelefono.getValor("EXT_ORG_ID"));
            filaUltimoRegistro.adicionar("ORG_LOCATION", filaTelefono.getValor("ORG_LOCATION"));
            filaUltimoRegistro.adicionar("PHONE_TYPE", filaTelefono.getValor("PHONE_TYPE"));
            filaUltimoRegistro.adicionar("COUNTRY_CODE", filaTelefono.getValor("COUNTRY_CODE"));
            filaUltimoRegistro.adicionar("PHONE", filaTelefono.getValor("PHONE"));
            filaUltimoRegistro.adicionar("EXTENSION", filaTelefono.getValor("EXTENSION"));
            filaUltimoRegistro.adicionar("PREF_PHONE_FLAG", "Y");
        }//

        bResult = insertarNuevoRegistroTelefonoEmpresa(filaUltimoRegistro);

        return bResult;
    }//

  private static Fila getUltimoRegistroTelefonoEmpresa(String strExtOrgId, String strOrgLocation, String strPhoneType) {
        Vector vDatos = new Vector();
        Fila filaDato = new Fila();
        try
        {
          String consulta = "SELECT PHN.EXT_ORG_ID, PHN.ORG_LOCATION, PHN.PHONE_TYPE, PHN.COUNTRY_CODE, PHN.PHONE, PHN.EXTENSION, PHN.PREF_PHONE_FLAG " +
                  "FROM PS_ORG_LOC_PHONES PHN " +
                  "WHERE PHN.EXT_ORG_ID = '"+strExtOrgId+"' " +
                  "AND PHN.ORG_LOCATION = '"+strOrgLocation+"' " +
                  "AND PHN.EFFDT = " +
                        "(SELECT MAX(PHN_IN.EFFDT) " +
                        "FROM PS_ORG_LOC_PHONES PHN_IN " +
                        "WHERE PHN_IN.EXT_ORG_ID = '"+strExtOrgId+"' " +
                        "AND PHN_IN.ORG_LOCATION = '"+strOrgLocation+"') " +
                  "AND PHN.PHONE_TYPE = '"+strPhoneType+"'";

          vDatos = EjecutaConsulta.Ejecutar(consulta);
          if (vDatos.size() > 0) {
              filaDato = (Fila) vDatos.firstElement();
          }//
        } catch (Exception e) {
          e.printStackTrace();
        }//
        return filaDato;
    }//

    private static boolean insertarNuevoRegistroTelefonoEmpresa(Fila filaRegistro) throws Exception {
        String strInsert = "INSERT INTO PS_ORG_LOC_PHONES";

        String strCampos="(EXT_ORG_ID, ORG_LOCATION, EFFDT, PREF_PHONE_FLAG";
        String strValues = " VALUES ('"+filaRegistro.getValor("EXT_ORG_ID")+"','"+filaRegistro.getValor("ORG_LOCATION")+"', SYSDATE, 'Y'";

        if(filaRegistro.getValor("PHONE_TYPE") != null){
            strCampos+=",PHONE_TYPE";
            strValues += ",'" + filaRegistro.getValor("PHONE_TYPE") + "'";
        }

        if(filaRegistro.getValor("COUNTRY_CODE") != null){
            strCampos+=",COUNTRY_CODE";
            strValues += ",'" + filaRegistro.getValor("COUNTRY_CODE") + "'";
        }

        if(filaRegistro.getValor("PHONE") != null){
            strCampos+=",PHONE";
            strValues += ",'" + filaRegistro.getValor("PHONE") + "'";
        }

        if(filaRegistro.getValor("EXTENSION") != null){
            strCampos+=",EXTENSION";
            strValues += ",'" + filaRegistro.getValor("EXTENSION") + "'";
        }

        strCampos += ")";
        strValues += ")";
        String strQuery=strInsert+strCampos+strValues;
        //System.out.println("INSERTAR PHONE LOC: " + strQuery);

        boolean bSalida=EjecutaConsulta.Insertar(strQuery);

        return bSalida;
    }

    public static Fila getContacto(String p_ext_org_id, String p_org_contact)
  {
    Vector datos = new Vector();
    Fila filaDatos = new Fila();
    try
    {
      String consulta = "SELECT EMP.EXT_ORG_ID, EMP.EFFDT, CONT.EFF_STATUS, EMPLID, CONT.ORG_CONTACT, " +
              "CONT.CONTACT_NAME AS NOMBRES, CONT.JOBTITLE AS NOMBRE_CARGO, CONT.EMAILID AS CORREO, " +
              "(SELECT PHN.PHONE from PS_ORG_CNTCT_PHN PHN WHERE PHN.EXT_ORG_ID = EMP.EXT_ORG_ID AND CONT.ORG_CONTACT = PHN.ORG_CONTACT AND (PHN.PHONE_TYPE = 'BUSN' OR PHN.PHONE_TYPE = 'WORK' OR PHN.PHONE_TYPE = 'MAIN') AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_CNTCT_PHN PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.ORG_CONTACT = CONT.ORG_CONTACT) AND ROWNUM <= 1) AS TELEFONO_OFICINA, " +
              "(SELECT PHN.PHONE from PS_ORG_CNTCT_PHN PHN WHERE PHN.EXT_ORG_ID = EMP.EXT_ORG_ID AND CONT.ORG_CONTACT = PHN.ORG_CONTACT AND (PHN.PHONE_TYPE = 'CELL') AND PHN.EFFDT = (SELECT MAX(PHN_IN.EFFDT) FROM PS_ORG_CNTCT_PHN PHN_IN WHERE PHN_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND PHN_IN.ORG_CONTACT = CONT.ORG_CONTACT) AND ROWNUM <= 1) AS NUMERO_CELULAR " +
              "FROM PS_EXT_ORG_TBL EMP, PS_ORG_CONTACT CONT " +
              "WHERE EMP.EXT_ORG_ID = CONT.EXT_ORG_ID " +
              "AND CONT.EFF_STATUS = 'A' " +
              "AND EMP.EXT_ORG_ID = '" + p_ext_org_id + "' " +
              "AND CONT.ORG_CONTACT = '" + p_org_contact + "' " +
              "AND EMP.EFFDT = (SELECT MAX(EMP_IN.EFFDT) FROM PS_EXT_ORG_TBL EMP_IN WHERE EMP_IN.EXT_ORG_ID = EMP.EXT_ORG_ID) " +
              "AND CONT.EFFDT = (SELECT MAX(CONT_IN.EFFDT) FROM PS_ORG_CONTACT CONT_IN WHERE CONT_IN.EXT_ORG_ID = EMP.EXT_ORG_ID AND CONT_IN.ORG_CONTACT = CONT.ORG_CONTACT)";


      datos = EjecutaConsulta.Ejecutar(consulta);

      if (datos.size() > 0) {
          filaDatos = (Fila) datos.firstElement();
      }
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getContactosPorEmpresa " + e.toString());
      e.printStackTrace();
    }
    return filaDatos;
  }
}